﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Threading;
using Word = Microsoft.Office.Interop.Word;
using Osobe;
using SkolskaUstanova;
using MolbeIPotvrde;

namespace ElektronskiDnevnik
{
    public partial class PregledPrisustvaPravdanjeR : Form
    {
        MySqlDataReader rdr = null;
        MySqlConnection conn;
        String dJMBG;
        List<Prisustvo> prisustva;
        List<Prisustvo> pravdanja;
        int brojOznacenihIzostanaka = 0;
        int brojac = 1;
        ListView.CheckedListViewItemCollection checkedItems;
        public PregledPrisustvaPravdanjeR(String maticni, MySqlConnection c)
        {
            InitializeComponent();
            dJMBG = maticni;
            conn = c;
            prisustva = new List<Prisustvo>();
            pravdanja = new List<Prisustvo>();
        }

        private void tabControl1_DrawItem(object sender, DrawItemEventArgs e)
        {
            Brush BackBrush = new SolidBrush(Color.AntiqueWhite);
            Brush ForeBrush = new SolidBrush(Color.Salmon);
            Font TabFont = new Font("Segoe Print", 10, FontStyle.Regular);
            string TabName = this.tabControl1.TabPages[e.Index].Text;
            StringFormat sf = new StringFormat();
            sf.Alignment = StringAlignment.Center;
            e.Graphics.FillRectangle(BackBrush, e.Bounds);
            Rectangle r = e.Bounds;
            r = new Rectangle(r.X, r.Y + 3, r.Width, r.Height - 3);
            e.Graphics.DrawString(TabName, TabFont, ForeBrush, r, sf);
            sf.Dispose();
            if (e.Index == this.tabControl1.SelectedIndex)
            {
                TabFont.Dispose();
                BackBrush.Dispose();
            }
            else
            {
                BackBrush.Dispose();
                ForeBrush.Dispose();
            }
        }


        private void Form8_Load(object sender, EventArgs e)
        {
            textBox3.Clear();
            textBox4.Clear();
            textBox5.Clear();
            int brojOpravdanih = 0;
            int brojNeopravdanih = 0;
            int ukupno = 0;
            MySqlCommand cmd = new MySqlCommand("SELECT * FROM PrisustvoPogled WHERE JMBG = '" + dJMBG + "';", conn);
            try
            {
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    String razlog, prisutan, opravdan, potvrda;
                    if (rdr["Razlog"] == null)
                        razlog = "";
                    else
                        razlog = Convert.ToString(rdr["Razlog"]);
                    if (rdr["Prisutan"] == null)
                        prisutan = "";
                    else
                        prisutan = Convert.ToString(rdr["Prisutan"]);
                    if (rdr["Opravdan"] == null)
                        opravdan = "";
                    else
                        opravdan = Convert.ToString(rdr["Opravdan"]);
                    if (rdr["Potvrda"] == null)
                        potvrda = "";
                    else
                        potvrda = Convert.ToString(rdr["Potvrda"]);
                    Prisustvo pr = new Prisustvo(Convert.ToInt32(rdr["IDPrisustva"]), Convert.ToDateTime(rdr["Datum"]), prisutan, opravdan, razlog, potvrda);
                    prisustva.Add(pr);
                }
                System.Windows.Forms.ListViewItem item = new System.Windows.Forms.ListViewItem();
                for (int i = 0; i < prisustva.Count(); i++)
                {
                    item = listView2.Items.Add(Convert.ToDateTime(prisustva[i].Datum).Day + "." + Convert.ToDateTime(prisustva[i].Datum).Month + "." + Convert.ToDateTime(prisustva[i].Datum).Year);
                    item.SubItems.Add(prisustva[i].Prisutan);
                    item.SubItems.Add(prisustva[i].Pravdanje);
                    item.SubItems.Add(prisustva[i].Potvrdjeno);
                    item.SubItems.Add(prisustva[i].Razlog);
                    if (prisustva[i].Prisutan == "ne")
                        ukupno++;
                    if (prisustva[i].Pravdanje == "da" && prisustva[i].Potvrdjeno == "da")
                        brojOpravdanih++;
                    if (prisustva[i].Pravdanje == "ne" && prisustva[i].Potvrdjeno == "da")
                        brojNeopravdanih++;

                }
                textBox4.Text = Convert.ToString(brojOpravdanih);
                textBox3.Text = Convert.ToString(brojNeopravdanih);
                textBox5.Text = Convert.ToString(ukupno);
                System.Windows.Forms.ListViewItem item1 = new System.Windows.Forms.ListViewItem();
                for (int i = 0; i < prisustva.Count(); i++)
                {
                    if (prisustva[i].Prisutan == "ne" && prisustva[i].Pravdanje == "" && (prisustva[i].Potvrdjeno == "" || prisustva[i].Potvrdjeno == "ne"))
                    {
                        item1 = listView1.Items.Add(Convert.ToString(prisustva[i].Sifra));
                        item1.SubItems.Add(Convert.ToDateTime(prisustva[i].Datum).Day + "." + Convert.ToDateTime(prisustva[i].Datum).Month + "." + Convert.ToDateTime(prisustva[i].Datum).Year);
                        item1.SubItems.Add(prisustva[i].Potvrdjeno);
                        pravdanja.Add(prisustva[i]);
                    }

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Greska", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            brojOznacenihIzostanaka = 0;
            textBox6.Clear();
            textBox7.Clear();
            brojac = 1;
            checkedItems = listView1.CheckedItems;
            foreach (ListViewItem item in checkedItems)
                brojOznacenihIzostanaka++;
            if (brojOznacenihIzostanaka == 0)
                MessageBox.Show("Niste nista oznacili!", "Greska", MessageBoxButtons.OK, MessageBoxIcon.Error);
            else
            {
                listView1.Enabled = false;
                textBox2.Text = ((checkedItems[0]).SubItems[1]).Text;
                textBox6.Text = Convert.ToString(brojac);
                textBox7.Text = Convert.ToString(brojOznacenihIzostanaka);
            }

        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (brojOznacenihIzostanaka == 0)
                MessageBox.Show("Niste nista oznacili!", "Greska", MessageBoxButtons.OK, MessageBoxIcon.Error);
            else
            {
                textBox1.Clear();
                if (brojac != brojOznacenihIzostanaka)
                {
                    textBox2.Text = ((checkedItems[brojac]).SubItems[1]).Text;
                    brojac++;
                    textBox6.Text = Convert.ToString(brojac);
                }
                else
                {
                    MessageBox.Show("Nema vise...", "Stop", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
           }

        private void button1_Click(object sender, EventArgs e)
        {
            if (brojOznacenihIzostanaka == 0)
                MessageBox.Show("Niste nista oznacili!", "Greska", MessageBoxButtons.OK, MessageBoxIcon.Error);
            else
            {
                MySqlCommand cmd;
                int sifra = Convert.ToInt32((checkedItems[brojac - 1]).Text);
                if (checkBox1.Checked == true && textBox1.Text == "")
                    MessageBox.Show("Trebate unijeti razlog izostajanja sa nastave.", "Upozorenje", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                else if (checkBox1.Checked == true && textBox1.Text != "")
                {
                    cmd = new MySqlCommand("UPDATE Prisustvo SET Pravdanje = 1, Razlog = '" + textBox1.Text + "' WHERE ID = " + sifra + ";", conn);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Razredniku je poslano da zelite pravdati izostanak.", "Obavijest", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    cmd = new MySqlCommand("UPDATE Prisustvo SET Pravdanje = 0, Razlog = '" + textBox1.Text + "' WHERE ID = " + sifra + ";", conn);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Razredniku je poslano da ne zelite pravdati izostanak", "Obavijest", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SaveFileDialog spasiMe = new SaveFileDialog();
            spasiMe.Filter = "pdf files (*.pdf)|*.pdf";
            spasiMe.FilterIndex = 2;
            spasiMe.RestoreDirectory = true;
            if (spasiMe.ShowDialog() == DialogResult.OK)
            {
                String s = spasiMe.FileName;
                Thread myWord = new Thread(delegate()
                {
                    KreirajIzvjestaj(conn, s, dJMBG);
                });
                myWord.Start();
            }
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            listView1.Enabled = true;
            brojOznacenihIzostanaka = 0;
            textBox2.Clear();
            textBox1.Clear();
            textBox6.Clear();
            textBox7.Clear();
            brojac = 1;
        }
        private void KreirajIzvjestaj(MySqlConnection myConnection, string myPath,
            string jmb)
        {
            Word._Application myWord = new Word.Application();
            Word._Document myDocument;
            myDocument = myWord.Documents.Add();

            String mySelect = "SELECT * FROM Skola;";
            MySqlCommand myCommand = new MySqlCommand(mySelect, conn);
            MySqlDataReader myReader = myCommand.ExecuteReader();

            myReader.Read();

            Word.Paragraph myParagraph;

            myParagraph = myDocument.Content.Paragraphs.Add();
            myParagraph.Range.Text = Convert.ToString(myReader.GetValue(0));
            myParagraph.Range.Font.Bold = 1;
            myParagraph.Format.IndentFirstLineCharWidth(1);
            myParagraph.Format.SpaceAfter = 0;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Content.Paragraphs.Add();
            String mjestoSk = Convert.ToString(myReader.GetValue(2));
            myParagraph.Range.Text = mjestoSk;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Content.Paragraphs.Add();
            myParagraph.Range.Text = Convert.ToString(myReader.GetValue(3));
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Content.Paragraphs.Add();
            myParagraph.Range.Text = Convert.ToString(myReader.GetValue(4)) + " "
                                 + Convert.ToString(myReader.GetValue(5));
            myParagraph.Format.SpaceAfter = 20;
            myParagraph.Range.InsertParagraphAfter();

            myReader.Close();

            mySelect = "SELECT * FROM UcenikRazredPogled WHERE JMBG='" + jmb + "';";
            myCommand = new MySqlCommand(mySelect, conn);
            myReader = myCommand.ExecuteReader();

            myReader.Read();

            myParagraph = myDocument.Paragraphs.Add();
            String myText = "Ime: ";
            myText = myText + Convert.ToString(myReader["Ime"]);
            myParagraph.Range.Font.Size = 10;
            myParagraph.Range.Font.Bold = 0;
            myParagraph.Range.Text = myText;
            myParagraph.Format.SpaceAfter = 0;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Paragraphs.Add();
            myText = "Prezime: ";
            myText = myText + Convert.ToString(myReader["Prezime"]);
            myParagraph.Range.Font.Size = 10;
            myParagraph.Range.Font.Bold = 0;
            myParagraph.Range.Text = myText;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Paragraphs.Add();
            myText = "Razred: ";
            myText = myText + Convert.ToString(myReader["Razred"]);
            myParagraph.Range.Font.Size = 10;
            myParagraph.Range.Font.Bold = 0;
            myParagraph.Range.Text = myText;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Paragraphs.Add();
            myText = "Odjeljenje: ";
            myText = myText + Convert.ToString(myReader["Odjeljenje"]);
            myParagraph.Range.Font.Size = 10;
            myParagraph.Range.Font.Bold = 0;
            myParagraph.Range.Text = myText;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Paragraphs.Add();
            myText = "Predmet: Svi predmeti";
            myParagraph.Range.Font.Size = 10;
            myParagraph.Range.Font.Bold = 0;
            myParagraph.Range.Text = myText;
            myParagraph.Format.SpaceAfter = 15;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph.Format.SpaceAfter = 0;
            myParagraph.Format.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;
            myParagraph.Range.Font.Size = 10;
            myParagraph.Format.SpaceBefore = 0;

            myReader.Close();

            mySelect = "SELECT count(*) FROM PrisustvoPogled WHERE JMBG='" + jmb + "';";
            myCommand = new MySqlCommand(mySelect, conn);
            myReader = myCommand.ExecuteReader();

            myReader.Read();
            int _brojRedova = Convert.ToInt32(myReader.GetValue(0));

            myReader.Close();

            Word.Table myTable;
            object oEnd = "\\endofdoc";

            Word.Range myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            myTable = myDocument.Tables.Add(myWordRange, 1, 4);
            myTable.Range.ParagraphFormat.SpaceAfter = 0;
            myTable.Spacing = 0;
            myTable.Cell(1, 1).Range.Text = "Datum";
            myTable.Cell(1, 2).Range.Text = "Prisutan";
            myTable.Cell(1, 3).Range.Text = "Opravdan";
            myTable.Cell(1, 4).Range.Text = "Razlog";
            myTable.Range.Rows[1].Shading.BackgroundPatternColor = Word.WdColor.wdColorGray15;

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            Word.Shape myShape;
            myShape = myDocument.Shapes.AddLine(-5, 1, 474, 1, myWordRange);
            myShape.Line.Weight = 1.5F;

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            myShape = myDocument.Shapes.AddLine(-5, 4, 474, 4, myWordRange);
            myShape.Line.Weight = 1;

            mySelect = "SELECT * FROM PrisustvoPogled WHERE JMBG='" + jmb + "';";
            myCommand = new MySqlCommand(mySelect, conn);
            myReader = myCommand.ExecuteReader();

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            if (_brojRedova > 0)
                myTable = myDocument.Tables.Add(myWordRange, _brojRedova, 4);
            myTable.Range.ParagraphFormat.SpaceAfter = 0;
            myTable.Range.Font.Bold = 0;
            for (int i = 1; i <= _brojRedova; i++)
            {
                myReader.Read();
                DateTime d = Convert.ToDateTime(myReader["Datum"]);
                String s = Convert.ToString(d.Day) + "." +
                           Convert.ToString(d.Month) + "." +
                           Convert.ToString(d.Year);
                myTable.Cell(i, 1).Range.Text = s;
                s = "Ne";
                if (Convert.ToInt32(myReader["Prisutan"]) == 1) s = "Da";
                myTable.Cell(i, 2).Range.Text = s;
                s = "Ne";
                if (myReader["Opravdan"] == System.DBNull.Value) s = "/";
                else
                {
                    if (Convert.ToInt32(myReader["Opravdan"]) == 1) s = "Da";
                }
                myTable.Cell(i, 3).Range.Text = s;
                if (myReader["Razlog"] == null) s = "";
                else s = Convert.ToString(myReader["Razlog"]);
                myTable.Cell(i, 4).Range.Text = s;
            }

            myReader.Close();

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            myShape = myDocument.Shapes.AddLine(-5, 1, 474, 1, myWordRange);
            myShape.Line.Weight = 1.5F;

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            myShape = myDocument.Shapes.AddLine(-5, 4, 474, 4, myWordRange);
            myShape.Line.Weight = 1;

            mySelect = "SELECT Broj FROM PrisustvoPoUceniku WHERE JMBG = '" +
                       jmb + "' AND Prisutan=0 AND Opravdan = 1;";
            myCommand = new MySqlCommand(mySelect, conn);
            myReader = myCommand.ExecuteReader();
            myReader.Read();

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            myTable = myDocument.Tables.Add(myWordRange, 2, 4);
            myTable.Range.ParagraphFormat.SpaceAfter = 0;
            myTable.Spacing = 0;
            myTable.Cell(1, 1).Range.Text = "Opravdanih";
            myTable.Cell(1, 2).Range.Text = "";
            myTable.Cell(1, 3).Range.Text = "";
            myTable.Cell(1, 4).Range.Text = Convert.ToString(myReader["Broj"]);

            myReader.Close();
            mySelect = "SELECT Broj FROM PrisustvoPoUceniku WHERE JMBG = '" +
                       jmb + "' AND Prisutan=0 AND Opravdan = 0;";
            myCommand = new MySqlCommand(mySelect, conn);
            myReader = myCommand.ExecuteReader();
            myReader.Read();
            myTable.Range.Rows.Shading.BackgroundPatternColor = Word.WdColor.wdColorGray15;

            myTable.Cell(2, 1).Range.Text = "Neopravdanih";
            myTable.Cell(2, 2).Range.Text = "";
            myTable.Cell(2, 3).Range.Text = "";
            myTable.Cell(2, 4).Range.Text = Convert.ToString(myReader["Broj"]);

            myReader.Close();
            String myString = "Sarajevo, " +
                       Convert.ToString(DateTime.Now.Day) + "." +
                       Convert.ToString(DateTime.Now.Month) + "." +
                       Convert.ToString(DateTime.Now.Year) + ".g.";
            myParagraph = myDocument.Paragraphs.Add();
            myParagraph.Format.SpaceAfter = 20;
            myParagraph.Format.SpaceBefore = 20;
            myParagraph.Range.Text = myString;
            myParagraph.Range.InsertParagraphAfter();

            myDocument.SaveAs(myPath, Word.WdSaveFormat.wdFormatPDF);
            myWord.Quit(false);

            System.Diagnostics.Process.Start(@myPath);
        }
    }
}
